package kr.oss.cho.util;

import java.io.BufferedReader;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;

import kr.oss.cho.mapper.SqlVeiwMapper;
import kr.oss.cho.vo.SqlInjectionVO;
import oracle.sql.CLOB;

public class StringUtils {
	
	public static  int checkSqlInjection( List<SqlInjectionVO> sqlList) throws Exception{
		
		List<SqlInjectionVO> resultList = new ArrayList<SqlInjectionVO>();
		int injectionCount = 0;
		final String orPattern = "(?i)(or)([\\s\\t]*[\\d\\w']+[\\s\\t]*)([=><])([\\s\\t]*[\\d\\w']+)";
		
		for (SqlInjectionVO vo : sqlList) {
			boolean injection = false;
			String  sqlFUllText= vo.getSQL_FULLTEXT().replaceAll("\n", " ").replaceAll("\'(?i)(or)", "' or").replaceAll("(?i)(or)\'", "or '");
			Matcher m = Pattern.compile(orPattern).matcher(sqlFUllText);
			System.out.println("확인용" + sqlFUllText);
			while(m.find()){
				
				
				String planText = m.group(2) + m.group(3) + m.group(4);
				
				String leftBind = m.group(2).trim().replaceAll("'", "");
				String operator = m.group(3);
				String rightBind = m.group(4).trim();
				
				if(rightBind.indexOf("'") > -1){
					
					rightBind = m.group(4).replaceAll("'", "");
					
					if(operator.equals("=")){
//						System.out.println(leftBind + "=" + rightBind);
//						System.out.println(leftBind.equals(rightBind));
						if(leftBind.equals(rightBind)){
							vo.getInjectionList().add(planText);
							injection = true;
						}
						
					}else if(operator.equals("<")){
//						System.out.println(leftBind + "<" + rightBind);
//						System.out.println(rightBind.compareTo(leftBind) > 0);
						if(rightBind.compareTo(leftBind) > 0){
							vo.getInjectionList().add(planText);
							injection = true;
						}
					}else if(operator.equals(">")){
//						System.out.println(leftBind + ">" + rightBind);
//						System.out.println(leftBind.compareTo(rightBind) > 0);
						if(leftBind.compareTo(rightBind) > 0){
							vo.getInjectionList().add(planText);
							injection = true;
						}
					}
				}
			}
			
			if(injection){
				resultList.add(vo);
			}
		}
		
		SqlSessionFactory sqlMapper =  SqlSessionFactoryUtil.getInstance();
		
		SqlSession session = sqlMapper.openSession();
		
			
		SqlVeiwMapper mapper = session.getMapper(SqlVeiwMapper.class);
		try{
			
			for (SqlInjectionVO vo : resultList) {
				
				//if(vo.getSQL_CHECK() == null || !vo.getSQL_CHECK().equals("N")){
					
					vo.setINJECTION(vo.getInjectionList().toString().replaceAll("[\\[\\]\\s]", ""));
					mapper.insertSqlInjection(vo);
					
				//}
				
			}
		injectionCount = mapper.countSqlInjection();
		}catch(Exception e){
			System.out.println(e.toString());
			session.rollback();
		}finally{
			session.commit();
			session.close();
		}
		
		
		return injectionCount;
	}
	
	public static String clobToString(CLOB clob) throws Exception{
		StringBuffer s = new StringBuffer();
		System.out.println("1");
        BufferedReader br = new BufferedReader(clob.getCharacterStream());
        System.out.println("2");
        String ts = "";
        while((ts = br.readLine()) != null) {
               s.append(ts + "\n");
        }
        br.close();
        return s.toString();
	}
	
	public static String getOracleErrorMessage(String errorMsg){
		String resultMsg = errorMsg.substring(errorMsg.indexOf("### Cause:"), errorMsg.length());
		return resultMsg;
	}
	
	public static String secondToHHMMSS(String secStr){
		
		String result = secStr;
		
		try{
			int sec = Integer.parseInt(secStr);
			int MM = 60;
			int HH = 60 * 60;
			
			String ss ="00", mm = "00", hh = "00";
			
			if(sec > HH){
				hh = String.format("%02d", sec / HH);
				sec = sec - (HH * (sec / HH));
			}
			if(sec > MM){
				mm = String.format("%02d", sec / MM);
				sec = sec - (MM * (sec / MM));
			}
			if(sec < 60){
				ss = String.format("%02d", sec);
			}
			result = hh+":"+mm+":"+ss;
			
		}catch (Exception e) {
			// TODO: handle exception
		}
		
		return result;
	}
	
	public static void main(String[] args){
		List<String> a = new ArrayList<String>();
		a.add(" or 1=1");
		a.add("b");
		a.add("c");
		System.out.println(a.toString().replaceAll("[\\[\\]\\s]", ""));
		System.out.println(secondToHHMMSS("4057"));
		//19243
		
		String aa = "org.apache.ibatis.exceptions.PersistenceException:\n"+ 

"### Error updating database.  Cause: java.sql.SQLException: ORA-00029: 사용자 세션이 아닙니다\n"+


"### The error may involve kr.oss.cho.mapper.SqlVeiwMapper.alterCurrentSessionKill-Inline\n"+

"### The error occurred while setting parameters\n"+

"### Cause: java.sql.SQLException: ORA-00029: 사용자 세션이 아닙니다";
		
		System.out.println(getOracleErrorMessage(aa));
	}
}
